USE MASTER 
GO 
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SuperSurvey') 
DROP DATABASE SuperSurvey 
GO 
CREATE DATABASE SuperSurvey
GO
USE SuperSurvey
GO
CREATE TABLE UserLogin
(
	Username nvarchar(100) PRIMARY KEY,
	[Password] nvarchar(100) NOT NULL,
	TimeLogin smalldatetime,
	TimeLogout smalldatetime,
	IP nvarchar(100),
	[Status] nvarchar(20) DEFAULT 'off' CHECK([Status] = 'on' OR  [Status] = 'off') 
)
GO
CREATE TABLE UserInformation
(
	Username nvarchar(100) PRIMARY KEY REFERENCES UserLogin(UserName),
	FullName nvarchar(100) NOT NULL,
	Birthday smalldatetime NOT NULL,
	Phone nvarchar(100) NOT NULL,
	Email nvarchar(100) NOT NULL,
	[Address] nvarchar(100) NOT NULL,
	Country nvarchar(100)
)
GO
-- My friends
CREATE TABLE Friend
(
	UserID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName),
	FriendID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName),
	[Status] nvarchar(20) DEFAULT 'none' CHECK([Status] = 'none' OR  [Status] = 'yes'),
	TimeFriend smalldatetime DEFAULT GETDATE(),
	CONSTRAINT pk_Friend PRIMARY KEY (UserID,FriendID) 
)
GO
-- Share my survey to my group..
CREATE TABLE UserGroup
(
	GroupID nvarchar(100) PRIMARY KEY,
	UserID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName),
	GroupName nvarchar(100) NOT NULL
)
GO
CREATE TABLE UserGroupMember
(
	GroupID nvarchar(100) FOREIGN KEY REFERENCES UserGroup(GroupID),
	MemberID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName)
	CONSTRAINT pk_UserGroupMember PRIMARY KEY(GroupID,MemberID)
)
GO
-- My survey -- chap nhan viec fi chuan 'UserID '.
CREATE TABLE Question
(
	QuestionID nvarchar(100) PRIMARY KEY,	
	UserID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName),
	CategoryQuestionName nvarchar(100) NOT NULL, -- Thuoc ve mang Giao Duc, Khao sat,  hay The Thao,v.v.v.v.
	QuestionType nvarchar(100) NOT NULL, -- Kieu checkbox, radiobutton hay textbox
	QuestionContent nvarchar(MAX) NOT NULL
)
GO
CREATE TABLE Answer
 (
	QuestionID nvarchar(100) FOREIGN KEY REFERENCES Question(QuestionID),
	MemberID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName),
	AnswerContent nvarchar(MAX) NOT NULL,
	CONSTRAINT pk_Answer PRIMARY KEY (QuestionID,MemberID)
 )
 GO

 CREATE TABLE Survey
(	
	SurveyID nvarchar(100) PRIMARY KEY,
	UserID nvarchar(100) FOREIGN KEY REFERENCES UserLogin(UserName),
	GroupID nvarchar(100) FOREIGN KEY REFERENCES UserGroup(GroupID),
	CountAnswer int DEFAULT 0, 	
	TimeCreate smalldatetime DEFAULT GETDATE(),
	TimeEnd smalldatetime NOT NULL CHECK(TimeEnd > GETDATE()),
	[Status] nvarchar(20) DEFAULT 'public' CHECK ([Status] = 'public' OR [Status]='private')
)
GO
  CREATE TABLE GroupQuestion
 (
	 SurveyID nvarchar(100) FOREIGN KEY REFERENCES Survey(SurveyID),
	 QuestionID nvarchar(100) FOREIGN KEY REFERENCES Question(QuestionID),
	 CONSTRAINT pk_GroupQuestion PRIMARY KEY (SurveyID,QuestionID)
 )
 GO